from sqlalchemy import (
    Column,
    Integer,
    String,
    ForeignKey,
    or_,
    and_,
)

from sqlalchemy.orm import relationship, declarative_base
from pprint import pprint
from en_url import engine

try:
    # 创建声明式的基类，所有的模型类都要继承自这个基类
    Base = declarative_base()

    # 定义User模型类
    class User(Base):
        # 指定数据库的表名为users
        __tablename__ = "users"
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        # 定义与Address表一对多的关系，back_populates指定反向引用
        addresses = relationship("Address", back_populates="user")

    class Address(Base):
        # 指定数据库的表名为users
        __tablename__ = "addresses"
        id = Column(Integer, primary_key=True)
        email_address = Column(String(50), nullable=False)
        user_id = Column(Integer, ForeignKey("users.id"))
        user = relationship("User", back_populates="addresses")

    # 在数据库中创建所有的定义的模型对应的表
    Base.metadata.create_all(engine)

except Exception as e:
    print(f"创建数据库表失败:{e}")

# 导入会话创建器
from sqlalchemy.orm import sessionmaker

# 创建会话工厂，绑定上面定义的数据库引擎
Session = sessionmaker(bind=engine)
# 创建会话对象
session = Session()

# 插入一条用户
# new_user = User(name="xiaowu", fullname="吴鹏丽")
# # 将新用户对象添加到会话中
# session.add(new_user)
# # 提交会话，将数据写入数据库
# session.commit()

# 查询所有的用户
# users = session.query(User).all()
# for user in users:
#     print("fullname", user.fullname, user.name)

#  filter_by适合简单等值查询 => 按条件查询用户
# user = session.query(User).filter_by(name="xiaowu", fullname="吴鹏丽").first()
# print(user.fullname)

# 复杂条件查询

# 还可以用or 做复杂查询
# 复杂查询案例一：查询姓名为zhang或者leichao的用户
# users = (
#     session.query(User).filter(or_(User.name == "zhang", User.name == "leichao")).all()
# )
# for user in users:
#     print(user.fullname)

# 复杂查询案例二：查询姓名为zhang或者leichao的用户
users = (
    session.query(User)
    .filter(and_(User.name == "leichao", User.fullname == "雷超"))
    .all()
)
for user in users:
    print(user.fullname)
#  按条件查询用户并且修改用户
user = session.query(User).filter_by(id=2).first()
pprint(user)
user.name = "leichao"
session.commit()

#  删除用户
# user = session.query(User).filter_by(name="ZHANG").first()
# session.delete(user)
# session.commit()

# 使用and_查询用户

# user = (
#     session.query(User)
#     .filter(and_(User.name == "ZHANG", User.fullname == "张三"))
#     .all()
# )
